<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Stream
 *
 * @author QuangKhoi
 */
class Stream {

    //put your code here
    /**
     *
     * @var Int
     */
    public $streamId;

    /**
     *
     * @var Int 
     */
    public $userId;

    /**
     *
     * @var String 
     */
    public $name;

    /**
     *
     * @var Int
     */
    public $type;

    /**
     *
     * @var String
     */
    public $query;

    /**
     *
     * @var Int 
     */
    public $language;

    /**
     *
     * @var Int
     */
    public $location;

    /**
     *
     * @var Int
     */
    public $platform;

    /**
     *
     * @var String
     */
    public $externalSources;

    /**
     *
     * @var Int
     */
    public $folderId;

    /**
     *
     * @var Date
     */
    public $dateCreated;

    /**
     * Default constructor
     */
    public function __construct() {
        
    }

    /**
     * Read folder infos from the database. Base64 streamId
     * @param Int $id
     */
    public static function withId($id) {
        $instance = new self();
        $db = Database::getInstance();
        $id = base64_decode($id);
        $result = $db->query("SELECT * FROM streams WHERE streamid = '$id'");
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow == 1) {
                $rows = $result->dataTable->rows[0];
                $instance->streamId = $rows["streamid"];
                $instance->userId = $rows["userid"];
                $instance->name = $rows["name"];
                $instance->type = $rows["type"];
                $instance->query = $rows["query"];
                $instance->language = $rows["language"];
                $instance->location = $rows["location"];
                $instance->platform = $rows["platform"];
                $instance->externalSources = $rows["externalsources"];
                $instance->folderId = $rows["folderid"];
                $dt = new DateTime($rows["datecreated"]);
                $instance->dateCreated = $dt->format('F d, Y');
                return $instance;
            } else {
                $controller = new ErrorController(new Error(), new ErrorView(), "404", "Not found any stream with provided id: $id");
                $controller->returnError();
                return NULL;
            }
        } else {
            $controller = new ErrorController(new Error(), new ErrorView, $result->returnCode, $result->message);
            $controller->returnError();
            return NULL;
        }
    }

    /**
     * Create new Folder object to store
     * @param Int $userid
     * @param String $folderName
     * @param Int $parentId
     */
    public static function withData($userid, $name, $type, $query, $language, $location, $platform, $externalSources, $folderId) {
        $instance = new self();
        $instance->userId = $userid;
        $instance->name = $name;
        $instance->type = $type;
        $instance->query = $query;
        $instance->language = $language;
        $instance->location = $location;
        $instance->platform = $platform;
        $instance->externalSources = $externalSources;
        $instance->folderId = $folderId;
        return $instance;
    }

    /**
     * 
     * @param type $mesageType
     * 1. All messages
     * 2. Archive
     * 3. Spam
     * 4. Trash
     * @return type
     */
    public function updateAccess($messageType = 0) {
        $db = Database::getInstance();
        $userId = User::getCurrentUserId();
        if ($this->getStreamInfo($messageType) == new DateTime('01-01-2000 00:00:00')) {
            $query = "INSERT INTO streamaccess (streamid, userid, lastaccess, classification) VALUES ($this->streamId, $userId, NOW(), $messageType)";
            $result = $db->query($query);
            return ($result->returnCode == 200);
        } else {
            $query = "UPDATE streamaccess SET lastaccess = NOW() WHERE streamid = $this->streamId AND userid = $userId AND classification = $messageType";
            $result = $db->query($query);
            return ($result->returnCode == 200);
        }
    }

    public function getStreamInfo($messageType = 0) {
        $db = Database::getInstance();
        $userId = User::getCurrentUserId();
        $query = "SELECT lastaccess from streamaccess WHERE streamid = $this->streamId AND userid = $userId AND classification = $messageType";
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow == 1) {
                $dt = $result->dataTable->rows[0][0];
                return new DateTime($dt);
            }
        }
        return new DateTime('01-01-2000 00:00:00');
    }

    /**
     * 
     * @param type $folderid
     * @return null
     */
    public static function listStream($folderid) {
        $db = Database::getInstance();
        $userId = $_SESSION["userid"];
        if ($folderid == 0) {
            $query = "SELECT * FROM streams WHERE userid IN (SELECT userid FROM users WHERE teamid = (SELECT teamid FROM users WHERE userid = $userId))";
        } else {
            $query = "SELECT * FROM streams WHERE folderid = '$folderid'";
        }
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                foreach ($result->dataTable->rows as $row) {
                    $id = $row['streamid'];
                    $name = $row['name'];
                    $folder = $row['folderid'];
                    $streamId = "$streamId$id;";
                    $streamname = "$streamname$name;";
                    $_folderid = "$_folderid$folder;";
                }
                return (object) array(
                    'streamid' => rtrim($streamId, ';'),
                    'streamname' => rtrim($streamname, ';'),
                    'folderid' => rtrim($_folderid, ';')
                );
            } else {
                //$controller = new ErrorController(new Error(), new ErrorView(), "404", "Not found any user with provided email");
                //$controller->returnError();
                return (object) array(
                    'streamid' => 'undefined',
                    'streamname' => 'undefined',
                    'folderid' => 'undefined'
                );
            }
        } else {
            //$controller = new ErrorController(new Error(), new ErrorView, $result->returnCode, $result->message);
            //$controller->returnError();
            return NULL;
        }
    }

    /**
     * Get all stream
     * @param type $folderid
     * @return null
     */
    public static function getStreams($folderid) {
        $db = Database::getInstance();
        $userId = $_SESSION["userid"];
        if ($folderid == 0) {
            $query = "SELECT * FROM streams WHERE userid IN (SELECT userid FROM users WHERE teamid = (SELECT teamid FROM users WHERE userid = $userId)) ORDER BY name";
        } else {
            $query = "SELECT * FROM streams WHERE folderid = '$folderid' ORDER BY name";
        }
        $result = $db->query($query);
        if ($result->returnCode == 200) {
            if ($result->dataTable->numOfRow > 0) {
                $streams = array();
                foreach ($result->dataTable->rows as $row) {
                    $stream = new Stream();
                    $stream->streamId = $row["streamid"];
                    $stream->userId = $row["userid"];
                    $stream->name = $row["name"];
                    $stream->type = $row["type"];
                    $stream->query = $row["query"];
                    $stream->language = $row["language"];
                    $stream->location = $row["location"];
                    $stream->platform = $row["platform"];
                    $stream->externalSources = $row["externalsources"];
                    $stream->folderId = $row["folderid"];
                    $stream->dateCreated = $row["datecreated"];
                    $streams[] = $stream;
                }
                return $streams;
            } else {
                return NULL;
            }
        } else {
            return NULL;
        }
    }

    public static function getMessages($streamId, $dateFrom, $dateTo, $tagId = 0, $sentiment = 0, $source = '0', $type = '0', $start, $length, $threadId = '') {
        $df = new DateTime($dateFrom);
        $dt = new DateTime($dateTo);
        $user = User::getLogedInUser();
        $dateFrom = $df->format("Y-m-d");
        $dateTo = $dt->format("Y-m-d");
        $sentimentCols = '';
        $score = '';
        $table = '';
        $stream = '';
        $date = '';
        $order = '';
        $where = '';
        $typeSQL = '';
        if ($sentiment > 0) {
            $sentimentCols = ", sentimentdata.streamid, sentimentdata.datecreated, sentimentdata.messageid, sentimentdata.threadid ";
            $score = - ($sentiment - 2);
            $score = "AND score = $score";
            $table = "sentimentdata";
            $stream = "AND sentimentdata.streamid = $streamId";
            $date = "datecreated >= '$dateFrom 00:00:00' AND datecreated <= '$dateTo 23:59:59'";
            $order = "ORDER BY sentimentdata.datecreated DESC, sentimentdata.threadid ASC";
        }
        if ($tagId != array('0')) {
            $tagCols = ", signals.messageid, signals.streamid, tagsdata.userid, tagsdata.tagid, date";
            $join = " LEFT JOIN tagsdata on tagsdata.signalid = signals.signalid LEFT JOIN sentimentdata ON sentimentdata.messageid = signals.messageid ";
            $where = "";
            $tagIds = implode(',', $tagId);
            $tags = "AND tagsdata.tagid IN ($tagIds)";
            $table = "signals";
            $userQuery = "AND tagsdata.userid IN (SELECT userid FROM users WHERE teamid = $user->teamId)";
            $stream = "AND sentimentdata.streamid = $streamId";
            $date = "sentimentdata.datecreated >= '$dateFrom 00:00:00' AND sentimentdata.datecreated <= '$dateTo 23:59:59'";
            $order = "ORDER BY sentimentdata.datecreated DESC, sentimentdata.threadid ASC";
        }
        if ($type != array('0')) {
            $types = implode(',', $type);
            $typeSQL = "AND sentimentdata.type IN ($types) ";
        }
        $threadSQL = '';
        if ($threadId > 0) {
            $threadSQL = 'AND threadid=' . $threadId;
        }
        $sourceSQL = '';
        if ($source != '0') {
            $source = explode(',', $source);
            $sources = implode('\',\'', $source);
            $sources = urldecode($sources);
            $sourceSQL = "AND sentimentdata.source IN ('$sources')";
        }
        $db = Database::getInstance();
        if ($user->type == '10')
            $userQuery = '';
        $query = "SELECT score$sentimentCols$tagCols FROM $table $join WHERE $date $typeSQL $sourceSQL $score $userQuery $stream $tags $order $threadSQL LIMIT $start, $length";
        $queryResult = $db->query($query);
        if ($queryResult->returnCode == 200) {
            if ($queryResult->dataTable->numOfRow > 0) {
                $messageId = array();
                foreach ($queryResult->dataTable->rows as $row) {
                    $messageId[] = $row['messageid'];
                }
                if (count($messageId) == 1)
                    return array(Message::withId($messageId));
                else
                    return Message::withId($messageId);
            }
        }
        return null;
    }

    public static function getPulse($streamId, $dateFrom, $dateTo, $start, $length, $source, $type, $tag, $sentiment) {
        $df = new DateTime($dateFrom);
        $dt = new DateTime($dateTo);
        $dateFrom = $df->format("Y-m-d");
        $dateTo = $dt->format("Y-m-d");
        $db = Database::getInstance();
        //Query threads
        $sourceSQL = '';
        if ($source != array('0')) {
            $sources = implode('\',\'', $source);
            $sourceSQL = "AND sentimentdata.source IN ('$sources')";
        }
        $tagJoin = '';
        $tagSQL = '';
        if ($tag != array('0')) {
            $tagJoin = 'LEFT JOIN signals ON sentimentdata.messageid = signals.messageid AND sentimentdata.streamid = signals.streamid LEFT JOIN tagsdata ON tagsdata.signalid = signals.signalid LEFT JOIN tags ON tags.tagid = tagsdata.tagid';
            $tags = implode(',', $tag);
            $tagSQL = "AND tagsdata.tagid IN ($tags)";
        }
        $typeSQL = '';
        if ($type != array('0')) {
            $types = implode(',', $type);
            $typeSQL = "AND sentimentdata.type IN ($types)";
        }
        $sentimentSQL = '';
        if (is_array($sentiment)) {
            $sentiments = implode(', ', $sentiment);
            $sentimentSQL = "AND score IN ($sentiments)";
        } else {
            if ($sentiment > 0) {
                $score = 2 - $sentiment;
                $sentimentSQL = "AND score = $score";
            }
        }
        $query = "SELECT threadid, COUNT( sentimentdata.messageid ) AS count, datecreated
                    FROM sentimentdata $tagJoin
                    WHERE sentimentdata.streamid = $streamId AND sentimentdata.datecreated >= '$dateFrom 00:00:00' AND sentimentdata.datecreated <= '$dateTo 23:59:59' $tagSQL $sourceSQL $typeSQL $sentimentSQL
                    GROUP BY sentimentdata.threadid
                    ORDER BY sentimentdata.datecreated DESC , sentimentdata.threadid DESC 
                    LIMIT $start, $length";
        $threadResult = $db->query($query);
        $threads = array();
        if ($threadResult->returnCode == 200) {
            if ($threadResult->dataTable->numOfRow > 0) {
                foreach ($threadResult->dataTable->rows as $row) {
                    if (!isset($threads[$row['threadid']]['replyCount']))
                        $threads[$row['threadid']]['replyCount'] = 0;
                    $threads[$row['threadid']]['replyCount'] += $row['count'];
                    $threads[$row['threadid']]['dateCreated'] = new DateTime($row['datecreated']);
                }
            } else
                return null;
        } else
            return null;
        //get 10 first replies
        $messageIds = array();
        $repliesCount = array();
        $total = 0;
        foreach ($threads as $threadId => $thread) {
            if ($thread['replyCount'] > 0) {
                $mIds = self::getReplies($threadId, $streamId, $dateFrom, $dateTo, 0, 0, $sentiment);
                $repliesCount[$threadId] = $mIds['repliesCount'];
                $total += array_sum($mIds['repliesCount']);
                for ($i = 0; $i < 10; $i++) {
                    $messageIds[] = $mIds['messageIds'][$i];
                }
            }
        }
        if (count($messageIds) == 1) {
            $messages = array(Message::withId($messageIds));
            $message = $messages[0];
            $message->totalReplies = $threads[$message->getThreadId()]['replyCount'];
            $message->replyCount = $repliesCount[$message->getThreadId()];
            $message->totalMessage = $total;
            $message->numberOfThreads = count($repliesCount);
            return $messages;
        } else {
            $messages = Message::withId($messageIds);
            foreach ($messages as $message) {
                $message->totalReplies = $threads[$message->getThreadId()]['replyCount'];
                $message->replyCount = $repliesCount[$message->getThreadId()];
                $message->totalMessages = $total;
                $message->numberOfThreads = count($repliesCount);
            }
            return $messages;
        }
    }

    public static function getReplies($threadId, $streamId, $dateFrom, $dateTo, $start, $length, $sentiment = 0) {
        $messageIds = array();
        $sentimentSQL = '';
        if ($sentiment != 0) {
            if (is_array($sentiment)) {
                $sentiments = implode(',', $sentiment);
                $sentimentSQL = "AND score IN ($sentiments)";
            } else {
                $score = 2 - $sentiment;
                $sentimentSQL = "AND score = $score";
            }
        }
        $query = "SELECT messageid, threadid, score FROM sentimentdata "
                . "WHERE streamid=$streamId AND threadid=$threadId "
                . "AND datecreated >= '$dateFrom 00:00:00' AND datecreated <= '$dateTo 23:59:59' $sentimentSQL "
                . "ORDER BY datecreated DESC ";
        if ($length > 0)
            $query .= "LIMIT $start, $length";
        $db = Database::getInstance();
        $queryResult = $db->query($query);
        $repliesCount = array();
        if ($queryResult->returnCode == 200) {
            if ($queryResult->dataTable->numOfRow > 0) {
                $repliesCount[0] = 0;
                $repliesCount[1] = 0;
                $repliesCount[-1] = 0;
                $repliesCount[-3] = 0;
                foreach ($queryResult->dataTable->rows as $row) {
                    $messageIds[] = $row['messageid'];
                    $repliesCount[$row['score']] += 1;
                }
            }
        }
        return array('messageIds' => $messageIds, 'repliesCount' => $repliesCount);
    }

}
